DROP TABLE IF EXISTS `sys_dept`;
CREATE TABLE `sys_dept` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT '部门名称',
  `order_num` int(11) DEFAULT NULL COMMENT '排序',
  `create_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
  `del_flag` char(1) DEFAULT '0' COMMENT '是否删除  -1：已删除  0：正常',
  `parent_id` int(11) DEFAULT NULL,
  `province_id` int(11) DEFAULT NULL COMMENT '部门绑定的省级城市ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='部门管理';

-- 开始插入默认数据
BEGIN;
INSERT INTO `sys_dept` VALUES
('1', '天津伟昌总后台管理', null, '2018-07-22 19:00:23', '2018-07-23 12:40:46', '0', '0', null ),
('2', '天津伟昌总后台客服部门', null, '2018-07-22 19:00:23', '2018-07-23 12:40:46', '0', '1', null ),
('3', '天津部门', null, '2018-07-22 19:00:23', '2018-07-23 12:40:46', '0', '2', '355'),
('4', '北京部门', null, '2018-07-22 19:00:23', '2018-07-23 12:40:46', '0', '2', '1');
COMMIT;


-- 部门关系表
BEGIN;
DROP TABLE IF EXISTS `sys_dept_relation`;
CREATE TABLE `sys_dept_relation` (
  `ancestor` int(11) NOT NULL COMMENT '祖先节点',
  `descendant` int(11) NOT NULL COMMENT '后代节点',
  PRIMARY KEY (`ancestor`,`descendant`),
  KEY `idx1` (`ancestor`),
  KEY `idx2` (`descendant`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC;
COMMIT;

-- 开始插入部门关系表数据
BEGIN;
INSERT INTO `sys_dept_relation` VALUES ('1', '1'),('1', '2'), ('1', '3'), ('1', '4'),
 ('2', '2'), ('2', '3'), ('2', '4');
COMMIT;

BEGIN;
DROP TABLE IF EXISTS `sys_role`;
CREATE TABLE `sys_role` (
  `role_id` int(11) NOT NULL AUTO_INCREMENT,
  `role_name` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `role_code` varchar(64) COLLATE utf8mb4_bin NOT NULL,
  `role_desc` varchar(255) COLLATE utf8mb4_bin DEFAULT NULL,
  `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `update_time` timestamp NULL DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `del_flag` char(1) COLLATE utf8mb4_bin DEFAULT '0' COMMENT '删除标识（0-正常,1-删除）',
  PRIMARY KEY (`role_id`),
  UNIQUE KEY `role_idx1_role_code` (`role_code`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ROW_FORMAT=DYNAMIC;
COMMIT;

BEGIN;
INSERT INTO `sys_role` VALUES ('1', 'admin', 'ROLE_ADMIN', '超级管理员', '2018-06-29 15:45:51', '2018-04-22 11:40:29', '0');
COMMIT;

BEGIN;
DROP TABLE IF EXISTS `sys_role_dept`;
CREATE TABLE `sys_role_dept` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `role_id` int(20) DEFAULT NULL COMMENT '角色ID',
  `dept_id` int(20) DEFAULT NULL COMMENT '部门ID',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='角色与部门对应关系';
COMMIT;


BEGIN;
INSERT INTO `sys_role_dept` VALUES ('1', '1', '1');
COMMIT;


DROP TABLE IF EXISTS `sys_user_role`;
CREATE TABLE `sys_user_role` (
  `user_id` int(11) NOT NULL COMMENT '用户ID',
  `role_id` int(11) NOT NULL COMMENT '角色ID',
  PRIMARY KEY (`user_id`,`role_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='用户角色表';

-- ----------------------------
--  Records of `sys_user_role`
-- ----------------------------
BEGIN;
INSERT INTO `sys_user_role` VALUES ('1', '1');
COMMIT;



-- 新增字段
BEGIN;

ALTER TABLE `w_demands`
ADD COLUMN `dept_id` int(20) DEFAULT NULL COMMENT '部门ID';
ALTER TABLE `w_shop`
ADD COLUMN `dept_id` int(20) DEFAULT NULL COMMENT '部门ID';
ALTER TABLE `w_shop_apply`
ADD COLUMN `dept_id` int(20) DEFAULT NULL COMMENT '部门ID';
ALTER TABLE `w_shop_user_relation`
ADD COLUMN `dept_id` int(20) DEFAULT NULL COMMENT '部门ID';
ALTER TABLE `w_user`
ADD COLUMN `dept_id` int(20) DEFAULT NULL COMMENT '部门ID';
COMMIT;
